<?php
/**
 * Cities Cutover Interface
 */

// Load Custover Information
use Fox\Ddic\DataElement\Formater\Alpha;
use Fox\Common\Localization\City;
use Fox\Html\Input;
use Fox\Html\Block;
use Fox\Html\TextArea;
use Fox\Html\CheckBox;
use Fox\Html\Screen;

set_time_limit(9500);
	
$screen = new Screen("cutover","execute");
$block = new Block("bCutover","Selection Criteria");
$block->add(new TextArea(array("comments","cityname")));
$block->add(new CheckBox("commit"));
$block->add(new Input("dbcnt",true,100));
$screen->add($block);
if($screen->submitted())
{
	$inputs = $screen->get();
	$created = $loaded = 0;
	if(!$screen->errors())
	{
		$handle = \Fox\Dao::infos(City::className())->handle();
		$query = $handle->createQueryBuilder();
		/*
		$query->select('cityid','keyname')
				->from('cities','t')
				->leftJoin('t','city_cutover_insee','c','t.zipcode = c.postal_code AND t.iso_code = c.iso_code')
				->setMaxResults($inputs['dbcnt']);
		$fragments[] = new Fox\Dao\SqlFragment("t.zipcode IS NULL ");
		$fragments[] = $block->item('cityname')->fragments();
		*/
		
		$query->select('c.cityid','c.cityname','c.postal_code','c.iso_code')
				->from('city_cutover_insee','c')
				->leftJoin('c','cities','t','t.zipcode = c.postal_code AND t.iso_code = c.iso_code')
				->setMaxResults($inputs['dbcnt']);
		$fragments[] = new Fox\Dao\SqlFragment("t.zipcode IS NULL ");
		/*
		foreach($result as $data)  {
			if(strlen($data['iso_code']) < 5) {
				// Add a 0 at the beginning of the code
				$data['iso_code'] = "0".$data['iso_code'];
				$handle->update('city_cutover_insee',array('iso_code' => $data['iso_code']),array('cityid' => $data['cityid']));
				
			} else {
				$handle->update('city_cutover_insee',array('notfounded' => 1),array('cityid' => $data['cityid']));
			}
		}
		*/
		/*
		$query->select('c.cityid','c.cityname','c.postal_code','c.iso_code')
				->from('city_cutover_insee','c')
				->setMaxResults($inputs['dbcnt']);
		$fragments[] = new Fox\Dao\SqlFragment("c.notfounded = ?",array(1));		
		*/
		
		$fragment = Fox\Dao\SqlFragment::combine($fragments);
		$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);
		$commit = $inputs['commit'];
		$loaded = count($result);
		
		foreach($result as $data)
		{
			$cityname = preg_replace("/ /","-",Fox\Utilities::cleanup($data['cityname']));
			
			$query = $handle->createQueryBuilder();
			// Try to found a city as match with the zipcode only
			$query->select('t.cityid','t.keyname','t.zipcode','t.iso_code')
				->from('cities','t');
			$fragment = new Fox\Dao\SqlFragment("t.zipcode = ? OR t.iso_code = ? OR t.keyname LIKE ? ",array($data['postal_code'],$data['iso_code'],$cityname));
			$founded = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);
			if(count($founded) == 1)  {
				$founded = $founded[0];
				// Might be an issue on my side
				\Fox\Message::Warning("Founded: Insee Data: $cityname {$data['postal_code']}-{$data['iso_code']} / {$founded['keyname']} {$founded['zipcode']}-{$founded['iso_code']} ");
				
			} else if(count($founded) == 0) {
				// Not Present at all
				\Fox\Message::Warning("Does not exist : {$data['cityid']} - {$data['postal_code']}  - $cityname");
				$city = new City();
				$city->setFields(array('zipcode' =>$data['postal_code'],
										'iso_code'  => $data['iso_code'],
										'fullname' => $data['cityname'],
										'insee_name' => $data['cityname'],
										'disable' => 0,
										'streets_status' => 0,
										'keyname' => preg_replace("/ /","-",$cityname))
								);
				$city->create();
				$handle->update('city_cutover_insee',array('notfounded' => 0),array('cityid' => $data['cityid']));
			}
			
		}
		/*
		foreach($result as $data)
		{
			$filter = null;
			$keyname = $data['keyname'];
			$keyname = preg_replace("/saint/","st",$keyname);
			$keyname = preg_replace("/\-/"," ",$keyname);
			
			// Find the city data in the insee db
			$query = \Fox\Dao::infos(City::className())->handle()->createQueryBuilder();
			$query->select('*')->from('city_cutover_insee','t');
			$filter = new Fox\Dao\SqlFragment("LOWER(cityname) = LOWER(?) ",array($keyname));
			$cityData = $filter->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);
			if(count($cityData) == 1)
			{
				// Check if street have been already loaded
				
				$cityData = $cityData[0];
				
				$fields['iso_code'] = $cityData['iso_code'];
				
				try {
					$city = new City($data['cityid']);
					$city->postal_code = '';
					$city->postal_code = sprintf("%05s",$cityData['postal_code']);
					$city->iso_code = $fields['iso_code'];
					if($commit)
					{
					//	$city->update();
						
					}
					\Fox\Message::Success("City $keyname has been updated ");
					$created++;
				}
				catch (Fox\Object\Exception\Save $e)
				{
					\Fox\Message::Error($e->getMessage());
					var_dump($e);
				}
			}
			else {
				\Fox\Message::Error("Mulitple Matches for city $keyname ");
			}
			unset($fields);
		}
		*/
		
	}
	\Fox\Message::Information("Entries loaded : $loaded");
	\Fox\Message::Information("Entries created : $created");
	
}
echo $screen->display();